package main

import (
	"database/sql"
	"errors"
	"fmt"
	"strconv"
	"strings"
)

func main() {
	var err error
	var sitkDB *sql.DB

	var idResult string
	//dbDsn=""
	err, sitkDB = OpenDB(dbDsn)
	if err != nil {
		fmt.Println(err.Error())
		return
	}
	fmt.Println("Test GetIDListViaSqlOP")
	err, idResult = GetIDListViaSqlOP(sitkDB)
	if err != nil {
		fmt.Println("Error:", err.Error())
	} else {
		fmt.Println("ID List:", idResult)
	}
	fmt.Println("Test GetIDListViaSqlOP")
	err, idResult = GetIDListViaCodeOP(sitkDB)
	if err != nil {
		fmt.Println("Error:", err.Error())
	} else {
		fmt.Println("ID List:", idResult)
	}
	_ = CloseDB(sitkDB)
}

func GetIDListViaSqlOP(sitkDB *sql.DB) (error, string) {
	var querySql string
	var dbData ReResultsByList
	querySql = `
		SELECT
		   GROUP_CONCAT(IF(tB.start_id=tB.end_id,start_id,CONCAT(start_id,"-",end_id)))
		FROM 
			(
			SELECT MIN(tA.id) AS start_id,
					 MAX(tA.id) AS end_id
			  FROM (SELECT id,
						   id - ROW_NUMBER() OVER (ORDER BY id) AS grp
					FROM orgTest01) AS tA
			  GROUP BY tA.grp
			) AS tB`
	dbData = GetRowByList(sitkDB, querySql)
	if dbData.ExecState != nil {
		return dbData.ExecState, ""
	}
	if dbData.RowsNum != 1 {
		return errors.New("get data error! "), ""
	}
	fmt.Println("Cost:", dbData.ExecCost)
	return nil, dbData.ReData[1][0]
}

func GetIDListViaCodeOP(sitkDB *sql.DB) (error, string) {
	var queryDataSql, queryTotalCountSql string
	var dbData ReResultsByList
	var idResult strings.Builder
	var idList []int
	var totalCount, idLstPos, errNums, tmpI, onePageRecs, idNums int
	var err error
	var tmpA int64
	onePageRecs = 500
	queryTotalCountSql = `SELECT COUNT(id) FROM orgTest01 ORDER BY id ASC`
	queryDataSql = `SELECT id FROM orgTest01 ORDER BY id ASC LIMIT ?,?`
	dbData = GetRowByList(sitkDB, queryTotalCountSql)
	if dbData.ExecState != nil {
		return dbData.ExecState, ""
	}
	if dbData.RowsNum == 0 {
		return errors.New("get totalCount, no record! "), ""
	}
	totalCount, err = strconv.Atoi(dbData.ReData[1][0])
	if err != nil {
		return errors.New("get totalCount ,data error! "), ""
	}
	for tmpI = 0; tmpI < totalCount; tmpI += onePageRecs {
		dbData = GetRowByList(sitkDB, queryDataSql, tmpI, onePageRecs)
		if dbData.ExecState != nil {
			tmpI -= onePageRecs
			errNums++
			if errNums == 3 {
				return err, idResult.String()
			}
			continue
		}
		if dbData.RowsNum == 0 {
			tmpI -= onePageRecs
			errNums++
			if errNums == 3 {
				return err, idResult.String()
			}
			continue
		}
		for tmpA = 1; tmpA <= dbData.RowsNum; tmpA++ {
			idNums, err = strconv.Atoi(dbData.ReData[tmpA][0])
			if err == nil {
				if idLstPos == 0 {
					idList = append(idList, idNums, idNums)
					idLstPos = 1
				} else {
					if idNums-idList[idLstPos] == 1 {
						idList[idLstPos] = idNums
					} else {
						idList = append(idList, idNums, idNums)
						idLstPos += 2
					}
				}
			}
		}
	}
	for tmpI = 0; tmpI < len(idList); tmpI += 2 {
		if idList[tmpI] == idList[tmpI+1] {
			idResult.WriteString(fmt.Sprintf("%d,", idList[tmpI]))
		} else {
			idResult.WriteString(fmt.Sprintf("%d-%d,", idList[tmpI], idList[tmpI+1]))
		}
	}
	fmt.Println("Cost:", dbData.ExecCost)
	return nil, strings.Trim(idResult.String(), ",")
}
